﻿Public Class frmProcesos
    Dim myAccesoDatos As New AccesoDatos
    Dim utilidades As New Utilidades

    Private Sub frmProcesos_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
        Me.Cursor = Cursors.WaitCursor
        Dim myConsulta As String

        If haySolicitudesParaAnular() = True Then
            myConsulta = "UPDATE SOLICITUDES_PRESTAMOS SET ID_ESTADO = 2, "
            myConsulta = myConsulta + "FECHA_ESTADO = '" + utilidades.obtenerFechaFormatoSQLServer2008(Format(Today, "dd/MM/yyyy")) + "' "
            myConsulta = myConsulta + "WHERE ID IN (SELECT ID FROM SOLICITUDES_PRESTAMOS S WHERE S.ID_ESTADO = 1 "
            myConsulta = myConsulta + "AND datediff (DAY, S.FECHA_ESTADO, getDate()) > 15 ) "
            myAccesoDatos.ejecutarConsulta(myConsulta)
        End If

        Me.txtClase1.Text = "0"
        Me.txtClase2.Text = "0"
        Me.txtClase3.Text = "0"

        If hayClientesClase1() = True Then
            myConsulta = "UPDATE CLIENTES SET ID_ESTADO_CLIENTE = 3 "
            myConsulta = myConsulta + "WHERE ID IN (SELECT CL.ID "
            myConsulta = myConsulta + "FROM PRESTAMOS P INNER JOIN CUOTAS C ON (P.ID = C.ID_PRESTAMO) "
            myConsulta = myConsulta + "INNER JOIN SOLICITUDES_PRESTAMOS S ON (P.ID_SOLICITUD = S.ID) "
            myConsulta = myConsulta + "INNER JOIN CLIENTES CL ON (S.ID_CLIENTE = CL.ID) "
            myConsulta = myConsulta + "WHERE C.PAGADA = 'N' AND getDate() > C.FECHA_VENCIMIENTO "
            myConsulta = myConsulta + "GROUP BY P.ID, CL.ID HAVING COUNT(C.NRO_CUOTA) <= 2 )"
            myAccesoDatos.ejecutarConsulta(myConsulta)
        End If

        If hayClientesClase2() = True Then
            myConsulta = "UPDATE CLIENTES SET ID_ESTADO_CLIENTE = 4 "
            myConsulta = myConsulta + "WHERE ID IN (SELECT CL.ID "
            myConsulta = myConsulta + "FROM PRESTAMOS P INNER JOIN CUOTAS C ON (P.ID = C.ID_PRESTAMO) "
            myConsulta = myConsulta + "INNER JOIN SOLICITUDES_PRESTAMOS S ON (P.ID_SOLICITUD = S.ID) "
            myConsulta = myConsulta + "INNER JOIN CLIENTES CL ON (S.ID_CLIENTE = CL.ID) "
            myConsulta = myConsulta + "WHERE C.PAGADA = 'N' AND getDate() > C.FECHA_VENCIMIENTO "
            myConsulta = myConsulta + "GROUP BY P.ID, CL.ID HAVING COUNT(C.NRO_CUOTA) = 3 )"
            myAccesoDatos.ejecutarConsulta(myConsulta)
        End If

        If hayClientesClase3() = True Then
            myConsulta = "UPDATE CLIENTES SET ID_ESTADO_CLIENTE = 5 "
            myConsulta = myConsulta + "WHERE ID IN (SELECT CL.ID "
            myConsulta = myConsulta + "FROM PRESTAMOS P INNER JOIN CUOTAS C ON (P.ID = C.ID_PRESTAMO) "
            myConsulta = myConsulta + "INNER JOIN SOLICITUDES_PRESTAMOS S ON (P.ID_SOLICITUD = S.ID) "
            myConsulta = myConsulta + "INNER JOIN CLIENTES CL ON (S.ID_CLIENTE = CL.ID) "
            myConsulta = myConsulta + "WHERE C.PAGADA = 'N' AND getDate() > C.FECHA_VENCIMIENTO "
            myConsulta = myConsulta + "GROUP BY P.ID, CL.ID HAVING COUNT(C.NRO_CUOTA) >= 4 )"
            myAccesoDatos.ejecutarConsulta(myConsulta)
        End If


        Me.Cursor = Cursors.Default
    End Sub

    Private Sub cmdAceptar_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles cmdAceptar.Click
        Me.Close()
    End Sub

    Private Function haySolicitudesParaAnular() As Boolean
        Dim haySolicitudes As Boolean = False
        Dim tabla As New Data.DataTable
        Dim myConsulta As String

        myConsulta = "SELECT COUNT(ID) FROM SOLICITUDES_PRESTAMOS WHERE ID_ESTADO = 1 "
        myConsulta = myConsulta + "AND datediff (DAY, FECHA_ESTADO, getDate()) > 15 "
        tabla = myAccesoDatos.ejecutarConsulta(myConsulta)

        If Not Convert.ToInt32(tabla.Rows.Item(0).Item(0)) = 0 Then
            haySolicitudes = True
        Else
            haySolicitudes = False
        End If

        Me.txtAnuladas.Text = Convert.ToString(tabla.Rows.Item(0).Item(0))

        Return haySolicitudes
    End Function

    Private Function hayClientesClase1() As Boolean
        Dim hayClientes As Boolean = False
        Dim tabla As New Data.DataTable
        Dim myConsulta As String

        myConsulta = "SELECT COUNT(DISTINCT CL.ID)  "
        myConsulta = myConsulta + "FROM PRESTAMOS P INNER JOIN CUOTAS C ON (P.ID = C.ID_PRESTAMO) "
        myConsulta = myConsulta + "INNER JOIN SOLICITUDES_PRESTAMOS S ON (P.ID_SOLICITUD = S.ID) "
        myConsulta = myConsulta + "INNER JOIN CLIENTES CL ON (S.ID_CLIENTE = CL.ID) "
        myConsulta = myConsulta + "WHERE C.PAGADA = 'N' AND getDate() > C.FECHA_VENCIMIENTO AND CL.ID_ESTADO_CLIENTE <> 3 "
        myConsulta = myConsulta + "GROUP BY P.ID, CL.ID HAVING COUNT(C.NRO_CUOTA) <= 2 "

        tabla = myAccesoDatos.ejecutarConsulta(myConsulta)

        If ((Not tabla Is Nothing) And (tabla.Rows.Count > 0)) Then
            If Not Convert.ToInt32(tabla.Rows.Item(0).Item(0)) = 0 Then
                Me.txtClase1.Text = Convert.ToString(tabla.Rows.Item(0).Item(0))
                hayClientes = True
            Else
                hayClientes = False
            End If
        Else
            hayClientes = False
        End If

        Return hayClientes
    End Function

    Private Function hayClientesClase2() As Boolean
        Dim hayClientes As Boolean = False
        Dim tabla As New Data.DataTable
        Dim myConsulta As String

        myConsulta = "SELECT COUNT(DISTINCT CL.ID)  "
        myConsulta = myConsulta + "FROM PRESTAMOS P INNER JOIN CUOTAS C ON (P.ID = C.ID_PRESTAMO) "
        myConsulta = myConsulta + "INNER JOIN SOLICITUDES_PRESTAMOS S ON (P.ID_SOLICITUD = S.ID) "
        myConsulta = myConsulta + "INNER JOIN CLIENTES CL ON (S.ID_CLIENTE = CL.ID) "
        myConsulta = myConsulta + "WHERE C.PAGADA = 'N' AND getDate() > C.FECHA_VENCIMIENTO AND CL.ID_ESTADO_CLIENTE <> 4 "
        myConsulta = myConsulta + "GROUP BY P.ID, CL.ID HAVING COUNT(C.NRO_CUOTA) = 3 "

        tabla = myAccesoDatos.ejecutarConsulta(myConsulta)

        If ((Not tabla Is Nothing) And (tabla.Rows.Count > 0)) Then
            If Not Convert.ToInt32(tabla.Rows.Item(0).Item(0)) = 0 Then
                Me.txtClase2.Text = Convert.ToString(tabla.Rows.Item(0).Item(0))
                hayClientes = True
            Else
                hayClientes = False
            End If
        Else
            hayClientes = False
        End If

        Return hayClientes
    End Function

    Private Function hayClientesClase3() As Boolean
        Dim hayClientes As Boolean = False
        Dim tabla As New Data.DataTable
        Dim myConsulta As String

        myConsulta = "SELECT COUNT(DISTINCT CL.ID) "
        myConsulta = myConsulta + "FROM PRESTAMOS P INNER JOIN CUOTAS C ON (P.ID = C.ID_PRESTAMO) "
        myConsulta = myConsulta + "INNER JOIN SOLICITUDES_PRESTAMOS S ON (P.ID_SOLICITUD = S.ID) "
        myConsulta = myConsulta + "INNER JOIN CLIENTES CL ON (S.ID_CLIENTE = CL.ID) "
        myConsulta = myConsulta + "WHERE C.PAGADA = 'N' AND getDate() > C.FECHA_VENCIMIENTO AND CL.ID_ESTADO_CLIENTE <> 5 "
        myConsulta = myConsulta + "GROUP BY P.ID, CL.ID HAVING COUNT(C.NRO_CUOTA) >= 4 "

        tabla = myAccesoDatos.ejecutarConsulta(myConsulta)

        If ((Not tabla Is Nothing) And (tabla.Rows.Count > 0)) Then
            If Not Convert.ToInt32(tabla.Rows.Item(0).Item(0)) = 0 Then
                Me.txtClase3.Text = Convert.ToString(tabla.Rows.Item(0).Item(0))
                hayClientes = True
            Else
                hayClientes = False
            End If
        Else
            hayClientes = False
        End If

        Return hayClientes
    End Function

End Class